-- 1. create database for dblink

create database IF NOT EXISTS dblink1;

-- 2. check dblink for FEDERATED plugin support.

show engines; -- FEDERATED

-- install plugin federated soname 'ha_federated.so';

-- 3. create server for dblink

drop server IF EXISTS dblink1;

CREATE SERVER IF NOT EXISTS dblink1 

FOREIGN DATA WRAPPER mysql 

OPTIONS (USER 'dbuser1', PASSWORD 'xxxxx' , HOST '192.168.0.101', PORT 3306, DATABASE 'db1');

-- 4. create table for dblink 

drop table IF EXISTS dblink1.test_table;

CREATE TABLE IF NOT EXISTS dblink1.code_system (

  "num" bigint(20) NOT NULL COMMENT '번호',

  "field1" varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '필드1',

  PRIMARY KEY ("num")

) ENGINE=FEDERATED DEFAULT CHARSET=utf8

CONNECTION='dblink1/test_table';

-- 5. test for dblink

select * from dblink1.test_table limit 10;

insert into dblink1.test_table (field1) values ('test');

update dblink1.test_table set field1='test222' where num=1;

select * from dblink1.test_table where num=1;